package com.and9.mchen.mcmusic.dao.impl;

import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.and9.mchen.mcmusic.constant.RelationTable;
import com.and9.mchen.mcmusic.dao.IAlbumDao;
import com.and9.mchen.mcmusic.dao.IBaseEntityDao;
import com.and9.mchen.mcmusic.entity.Album;
import com.and9.mchen.mcmusic.entity.Introduction;
import com.and9.mchen.mcmusic.entity.MusicStyle;
import com.and9.mchen.mcmusic.entity.Singer;
import com.and9.mchen.mcmusic.entity.Song;
import com.and9.mchen.mcmusic.entity.Statistics;
import com.and9.mchen.mcmusic.exception.DBException;
import com.and9.mchen.mcmusic.exception.DaoException;
import com.and9.mchen.mcmusic.util.EntityHelper;
import com.and9.mchen.mcmusic.util.GEntityHelper;
import com.and9.mchen.mcmusic.util.QueryHelper;

public class AlbumDaoImpl implements IAlbumDao{

	private final static Log log = LogFactory.getLog(AlbumDaoImpl.class);

	public static String TABLEMANE="album_table";
	
	public int createAlbumImageUrlRelevanceByAlbum(Album album)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album.getImageUrl())){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, "id="+album.getId(), "image_url_id");
		
		try {
			return QueryHelper.update(sqlStr, album.getImageUrl().getId());
		} catch (DBException e) {
			log.error("[function: createAlbumImageUrlRelevanceByAlbum]", e);
			throw new DaoException("create album image url relevance by album faliure");
		}
	}

	public int createAlbumSingerRelevanceByAlbum(Album album)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album.getSinger())){
			return -1;
		}
		
		Singer singer=album.getSinger();
		
		if(singer.getId().equals(1L)){
			return 0;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, "id="+album.getId(), "singer_id");
		
		try {
			return QueryHelper.update(sqlStr, singer.getId());
		} catch (DBException e) {
			log.error("[function: createAlbumSingerRelevanceByAlbum]", e);
			throw new DaoException("create album singer relevance by album faliure");
		}
		
	}

	public int[] createAlbumSongRelevanceByAlbum(Album album)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				GEntityHelper.isEmpty(album.getSongs())){
			return null;
		}
		
		Set<Song> songs=(Set<Song>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(album.getSongs());
		
		if(GEntityHelper.isEmpty(songs)){
			return null;
		}
		
		Object[][] params = EntityHelper.initRelevanceParamsById(album, songs);
		
		String sqlStr=QueryHelper.getInsertSQLByProperty(RelationTable.ALBUM_SONG_TABLE, 
				"album_id", "song_id");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: createAlbumSongRelevanceByAlbum]", e);
			throw new DaoException("create album song relevance by album faliure");
		}
	
	}

	public int createOrUpdateAlbum(Album album, String... propertyNames)
			throws DaoException {
		if(GEntityHelper.isEmpty(album)){
			return -1;
		}
		
		String sqlStr=null;
		Object[] propertyValues=null;
		
		if(album.getId()==null || album.getId() <=0){
			
			if(GEntityHelper.isEmpty(album.getUniqueId())){
				album.setUniqueId(EntityHelper.createUniqueId());
			}
			
			album.setCreateTime(new Timestamp(System.currentTimeMillis()));
			Object[] parentParams=QueryHelper.getFieldsVal(album.getParent(), IBaseEntityDao.BASEPROPERTYNAMES);
			Object[] albumParams=QueryHelper.getFieldsVal(album, AlbumDaoImpl.BASEPROPERTYNAMES);
			propertyValues=EntityHelper.groupArrays(parentParams, albumParams);
			propertyNames=EntityHelper.groupArrays(IBaseEntityDao.BASEPROPERTYNAMES, AlbumDaoImpl.BASEPROPERTYNAMES);
			sqlStr=QueryHelper.getInsertSQLByProperty(AlbumDaoImpl.TABLEMANE, propertyNames);
			
		}else {
			if(GEntityHelper.isEmpty(propertyNames) || 
					EntityHelper.containBaseEntityPropertyName(propertyNames) == true){
				propertyNames=IAlbumDao.BASEPROPERTYNAMES;
			}
			propertyValues=QueryHelper.getFieldsVal(album, propertyNames);
			sqlStr=QueryHelper.getUpdateSQL(AlbumDaoImpl.TABLEMANE, album,"id="+album.getId(), propertyNames);
		}

		try {
			return QueryHelper.update(sqlStr, propertyValues);
		} catch (DBException e) {
			log.error("[function: createOrUpdateAlbum]", e);
			throw new DaoException("create or update album faliure");
		}
		
	}

	public int deleteAlbumByAlbumId(long id) throws DaoException {
		
		if(id <= 0){
			return -1;
		}

		String sqlStr=QueryHelper.getDeleteSQL(AlbumDaoImpl.TABLEMANE, "id=?");
		
		try {
			return QueryHelper.update(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: deleteAlbumByAlbumId]", e);
			throw new DaoException("delete album by album id faliure");
		}
	}

	public Album getAlbumByAlbumId(long id) throws DaoException {
		
		if(id <= 0){
			return null;
		}
		
		String sqlStr=QueryHelper.getSelectSQL(AlbumDaoImpl.TABLEMANE, "id=?");
		
		try {
			return QueryHelper.read(Album.class, sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getAlbumByAlbumId]", e);
			throw new DaoException("get album by album id faliure");
		}
		
	}

	public Album getAlbumByAlbumUniqueId(String uniqueId) throws DaoException {

		if(GEntityHelper.isEmpty(uniqueId)){
			return null;
		}
		
		String sqlStr=QueryHelper.getSelectSQL(AlbumDaoImpl.TABLEMANE, "uniqueId=?");
		
		try {
			return QueryHelper.read(Album.class, sqlStr, uniqueId);
		} catch (DBException e) {
			log.error("[function: getAlbumByAlbumUniqueId]", e);
			throw new DaoException("get album by album unique id faliure");
		}
		
	}

	public List<Album> getAlbumsBySingerId(long id, int pageNum, int pageCount,
			String orderBy) throws DaoException {

		if(id <= 1){
			return null;
		}
		
		if(GEntityHelper.isEmpty(orderBy)){
			orderBy=MessageFormat.format("{0}.isStick DESC, {0}.supprotCount DESC", 
					StatisticsDaoImpl.TABLEMANE);
		}
		
		String sqlStr=MessageFormat.format("SELECT {0}.* FROM {0}, {1} WHERE " +
				"{0}.statistics_id={1}.id AND {0}.singer_id=? ORDER BY {2}", AlbumDaoImpl.TABLEMANE, 
					StatisticsDaoImpl.TABLEMANE, orderBy);
		
		try {
			return QueryHelper.query_slice(Album.class, sqlStr, pageNum, pageCount, id);
		} catch (DBException e) {
			log.error("[function: getAlbumsBySingerId]", e);
			throw new DaoException("get albums by singer id faliure");
		}

	}

	public long getAlbumsCountBySingerId(long id) throws DaoException {
		
		if(id <= 0){
			return -1;
		}
		if(id == 1){
			return 0;
		}
		
		String sqlStr=MessageFormat.format("SELECT COUNT({0}.id) FROM {0} WHERE {0}.singer_id=?", AlbumDaoImpl.TABLEMANE);
		
		try {
			return QueryHelper.stat(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getAlbumsCountBySingerId]", e);
			throw new DaoException("get albums count by singer id faliure");
		}
		
	}

	public int removeAlbumImageUrlRelevanceByAlbum(Album album)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) ||
			!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album.getImageUrl())){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, "id="+album.getId(), "image_url_id");
		
		try {
			return QueryHelper.update(sqlStr, 1);
		} catch (DBException e) {
			log.error("[function: removeAlbumImageUrlRelevanceByAlbum]", e);
			throw new DaoException("remove album image url relevance by album faliure");
		}
	}

	public int removeAlbumSingerRelevanceByAlbum(Album album)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album.getSinger())){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, "id="+album.getId(), "singer_id");
		
		try {
			return QueryHelper.update(sqlStr, 1);
		} catch (DBException e) {
			log.error("[function: removeAlbumSingerRelevanceByAlbum]", e);
			throw new DaoException("remove album singer relevance by album faliure");
		}
	}

	public int[] removeAlbumSongRelevanceByAlbum(Album album)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				GEntityHelper.isEmpty(album.getSongs())){
			return null;
		}
		
		Set<Song> songs=(Set<Song>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(album.getSongs());
		
		if(GEntityHelper.isEmpty(songs)){
			return null;
		}
		
		Object[][] params = EntityHelper.initRelevanceParamsById(album, songs);
		
		String sqlStr=QueryHelper.getDeleteSQL(RelationTable.ALBUM_SONG_TABLE, 
				"album_id=? AND song_id=?");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: removeAlbumSongRelevanceByAlbum]", e);
			throw new DaoException("remove album song relevance by album faliure");
		}
	}

	public Album getAlbumBySingerSongId(long singer_id, long song_id)
			throws DaoException {
		
		if( singer_id <= 1 || song_id <=0 ){
			return null;
		}
		
		String sqlStr=MessageFormat.format("SELECT {0}.* FROM {0}, {1} WHERE " +
				"{0}.statistics_id={1}.id AND {0}.singer_id=? AND {0}.id IN " +
					"(SELECT DISTINCT {2}.album_id FROM {2} WHERE {2}.song_id=? ) " +
						"ORDER BY {1}.isStick DESC, {1}.clickCount DESC, " +
							"{0}.issueTime DESC LIMIT 1", AlbumDaoImpl.TABLEMANE, 
								StatisticsDaoImpl.TABLEMANE, 
									RelationTable.ALBUM_SONG_TABLE);
		
		try {
			return QueryHelper.read(Album.class, sqlStr, singer_id, song_id);
		} catch (DBException e) {
			log.error("[function: getAlbumBySingerSongId]", e);
			throw new DaoException("get album by singer song id faliure");
		}
		
	}

	public int[] createAlbumMusicStyleRelevanceByAlbum(Album album)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				GEntityHelper.isEmpty(album.getMusicStyles())){
			return null;
		}
		
		Set<MusicStyle> musicStyles=(Set<MusicStyle>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(album.getMusicStyles());
		
		if(GEntityHelper.isEmpty(musicStyles)){
			return null;
		}
		
		Object[][] params = EntityHelper.initRelevanceParamsById(album, musicStyles);
		
		String sqlStr=QueryHelper.getInsertSQLByProperty(RelationTable.ALBUM_MUSIC_STYLE_TABLE, 
				"album_id", "music_style_id");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: createAlbumMusicStyleRelevanceByAlbum]", e);
			throw new DaoException("create album music style relevance by album faliure");
		}
	}

	public int[] removeAlbumMusicStyleRelevanceByAlbum(Album album)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				GEntityHelper.isEmpty(album.getMusicStyles())){
			return null;
		}
		
		Set<MusicStyle> musicStyles=(Set<MusicStyle>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(album.getMusicStyles());
		
		if(GEntityHelper.isEmpty(musicStyles)){
			return null;
		}
		
		Object[][] params = EntityHelper.initRelevanceParamsById(album, musicStyles);
		
		String sqlStr=QueryHelper.getDeleteSQL(RelationTable.ALBUM_MUSIC_STYLE_TABLE, 
				"album_id=? AND music_style_id=?");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: removeAlbumMusicStyleRelevanceByAlbum]", e);
			throw new DaoException("remove album music style relevance by album faliure");
		}
	}

	public int createAlbumStatisticsRelevanceByAlbum(Album album)
			throws DaoException {

		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album.getStatistics())){
			return -1;
		}
		
		Statistics statistics=album.getStatistics();
		
		String sqlStr=MessageFormat.format("UPDATE {0} SET {0}.statistics_id=? WHERE {0}.id=?", 
				AlbumDaoImpl.TABLEMANE);
		
		try {
			return QueryHelper.update(sqlStr, statistics.getId(), album.getId());
		} catch (DBException e) {
			log.error("[function: createAlbumStatisticsRelevanceByAlbum]", e);
			throw new DaoException("create album statistics relevance by album faliure");
		}
		
	}

	public int removeAlbumStatisticsRelevanceByAlbum(Album album)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album)){
			return -1;
		}
		
		String sqlStr=MessageFormat.format("UPDATE {0} SET {0}.statistics_id=? WHERE {0}.id=?", 
				AlbumDaoImpl.TABLEMANE);
		
		try {
			return QueryHelper.update(sqlStr, 1L, album.getId());
		} catch (DBException e) {
			log.error("[function: removeAlbumStatisticsRelevanceByAlbum]", e);
			throw new DaoException("remove album statistics relevance by album faliure");
		}
	}

	public int createAlbumrIntroductionRelevanceByAlbum(Album album)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album.getIntroduction())){
			return -1;
		}
		
		Introduction introduction=album.getIntroduction();
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, 
				"id=?", "introduction_id");
		
		try {
	
			return QueryHelper.update(sqlStr, introduction.getId(), album.getId());
		} catch (DBException e) {
			log.error("[function: createAlbumrIntroductionRelevanceByAlbum]", e);
			throw new DaoException("create album introduction relevance by album failure");
		}
		
	}

	public int removeAlbumIntroductionRelevanceByAlbum(Album album)
			throws DaoException {

		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(album)){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(AlbumDaoImpl.TABLEMANE, 
				"id=?", "introduction_id");
		
		try {
			
			return QueryHelper.update(sqlStr, 1L, album.getId());
		} catch (DBException e) {
			log.error("[function: removeAlbumIntroductionRelevanceByAlbum]", e);
			throw new DaoException("remove album introduction relevance by album failure");
		}
		
	}

	public List<Album> getAlbums(String sql, int pageNum, int pageCount,
			Object... params) throws DaoException {
		
		if(GEntityHelper.isEmpty(sql)){
			return null;
		}
		
		try {
			return QueryHelper.query_slice(Album.class, sql, pageNum, pageCount, params);
		} catch (DBException e) {
			log.error("[function: getAlbums]", e);
			throw new DaoException("get albums by sql failure");
		}
	}
	
}
